#!/usr/bin/env python3
# -*- encoding: utf-8 -*-
"""
@File    :   sql.py
@Time    :   2024-07-09 19:57
@Author  :   坐公交也用券
@Version :   1.0
@Contact :   faith01238@hotmail.com
@Homepage : https://liumou.site
@Desc    :   当前文件作用
"""
import logging
import sqlite3
import sys

# 创建一个自定义的日志记录器

logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)

# 创建一个自定义的日志处理器，设置其输出格式
formatter = logging.Formatter('%(asctime)s [%(levelname)s] %(filename)s:%(lineno)d | %(funcName)s | %(message)s')
handler = logging.StreamHandler(sys.stdout)
handler.setFormatter(formatter)
logger.addHandler(handler)


class Db:
	def __init__(self, file: str, debug=False):
		"""

		:param file:
		"""
		self.debug = debug
		self.file = file
		self.db = sqlite3.connect(self.file)
		self.cursor = self.db.cursor()
		self.create()

	def create(self):
		"""
		创建数据表
		:return:
		"""
		try:
			self.cursor.execute('''CREATE TABLE IF NOT EXISTS bt(id INTEGER PRIMARY KEY,p TEXT, format TEXT)''')
			self.db.commit()
			logger.info('创建数据表成功')
		except Exception as e:
			logger.error(e)

	def add(self, p, format_):
		"""
		添加数据
		:param p:
		:param format_:
		:return:
		"""
		if self.exists(p, format_):
			return
		try:
			self.cursor.execute('''INSERT INTO bt(p, format) VALUES (?, ?);''', (p, format_))
			self.db.commit()
		except Exception as e:
			logger.error(e)
		finally:
			# self.db.close()
			logger.info('添加数据成功')

	def delete(self, p, format_):
		"""
		删除数据
		:return:
		"""
		if not self.exists(p, format_):
			return
		sql = '''DELETE FROM bt WHERE ''' + f"p='{p}' and format='{format_}';"
		if self.debug:
			logger.debug(sql)
		try:
			self.cursor.execute(sql)
			self.db.commit()
		except Exception as e:
			logger.error(e)
			return
		logger.info(f'删除数据成功: {p} {format_}')

	def exists(self, p, format_):
		"""
		查询数据是否存在
		:param p: 需要查询的路径
		:param format_: 需要查询的格式
		:return:
		"""
		sql = '''SELECT * FROM bt WHERE ''' + f"p='{p}' and format='{format_}';"
		if self.debug:
			logger.debug(sql)
		self.cursor.execute(sql)
		results = self.cursor.fetchall()
		if results:
			logger.info(f'数据已存在: {p} {format_}')
			return True
		else:
			logger.info(f'数据不存在: {p} {format_}')
			return False


if __name__ == '__main__':
	db = Db('db.sqlite3')
	db.debug = True
	db.create()
	db.add('/data/', 'iso')
	if db.exists('/data/', 'iso'):
		print('数据已存在')
		# db.delete('/data/', 'iso')
# db = sqlite3.connect('db.sqlite3')
# cursor = db.cursor()
# cursor.execute('''
#             CREATE TABLE IF NOT EXISTS bt(
#                 id INTEGER PRIMARY KEY,
#                 p TEXT,
#                 format TEXT)''')
# db.commit()
# cursor.execute('''INSERT INTO bt(p, format) VALUES (?, ?);''', ('/data/', 'iso'))
# cursor.execute('''INSERT INTO bt(p, format) VALUES (?, ?);''', ('/data1/', 'txt'))
# db.commit()
#
#
# def search_db():
# 	cursor.execute("SELECT * FROM bt")
# 	results = cursor.fetchall()
# 	print('Results Found...\n')
# 	print(results)
#
#
# search_db()
